package teamCservlets;
import java.io.Serializable;
import java.sql.Time;
import java.text.SimpleDateFormat;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.ResultSet;
import java.io.IOException;
import java.util.logging.FileHandler;
import java.util.logging.Handler;
import java.util.logging.Level;
import java.util.logging.Logger;
import java.util.ArrayList;
import java.sql.Types;

public class DatabaseInterfaceDefect extends DatabaseInterface {

	// constructor.

	public DatabaseInterfaceDefect () {
		super();
	} // End constructor

	// The getDefect method loads an Defect by defect ID.
	// If an defect with the specified ID is found, it returns an
	// DefectBean object.  Otherwise it returns "null".
	public synchronized DefectBean getDefect(int defectID) {

		try {

			// Execute query.

			String defectQuery = new String("SELECT * FROM DEFECT WHERE Defect_ID = ?");

			LogInfo(defectQuery);

			preparedStatement = connection.prepareStatement(defectQuery);
			preparedStatement.setInt(1,defectID);

			runSQLstatement();

			// If result is null, return null.  Otherwise, generate a DefectBean
			// object and return it.

			if(resultSet == null) {
				LogWarning("DatabaseInterfaceDefect: Result set is null");
				return null;
			} else {
				if(!resultSet.isBeforeFirst()) {
					LogWarning("DatabaseInterfaceDefect: Result set is empty!");
					return null;
				}
				// Create new DefectBean object
				DefectBean defect = new DefectBean();
				resultSet.first();

				// Get the query results.
				defect.setDefectID(resultSet.getInt("Defect_ID"));
				defect.setApplicationID(resultSet.getInt("Application_ID"));
				defect.setSubmitterID(resultSet.getInt("Submitter_ID"));
				defect.setAssigneeID(resultSet.getInt("Assignee_ID"));
				defect.setStatus(resultSet.getString("Status"));
				defect.setPriority(resultSet.getString("Priority"));
				defect.setSummary(resultSet.getString("Summary"));
				defect.setDetailedDescription(resultSet.getString("Detailed_Description"));
				defect.setResolutionNote(resultSet.getString("Final_Resolution"));
				defect.setStringOpenDate(resultSet.getString("Open_Date"));
				defect.setStringResolveDate(resultSet.getString("Closed_Date"));

				// Cleanup the connection and statement by closing them.

				cleanup();


				return defect;
			} // End code that returns DefectBean

		} catch (SQLException sqle) {

			LogException(sqle);
			return null;
		} catch (NullPointerException npe) {

			LogException(npe);
			return null;
		} catch (Exception e) {
			LogException(e);
			return null;
		}

	} // End getDefect method

	// The getOpenDefect method loads an Defect by defect ID, only if its state
	// is not CLOSED.
	// If an defect with the specified ID is found, it returns an
	// DefectBean object.  Otherwise it returns "null".
	public synchronized DefectBean getOpenDefect(int defectID) {

		try {

			// Execute query.

			String defectQuery = new String("SELECT * FROM DEFECT WHERE Defect_ID = ? " +
					"and STATUS <> 'CLOSED'");

			LogInfo(defectQuery);

			preparedStatement = connection.prepareStatement(defectQuery);
			preparedStatement.setInt(1,defectID);

			runSQLstatement();

			// If result is null, return null.  Otherwise, generate a DefectBean
			// object and return it.

			if(resultSet == null) {
				LogWarning("DatabaseInterfaceDefect: Result set is null");
				return null;
			}
				// Create new DefectBean object

				DefectBean defect = new DefectBean();
				if(!resultSet.isBeforeFirst()) {
					LogWarning("DatabaseInterfaceDefect: Empty result set so return null for DefectBean");
					return null;
				}
				resultSet.first();


				// Get the query results.

				defect.setDefectID(resultSet.getInt("Defect_ID"));
				defect.setApplicationID(resultSet.getInt("Application_ID"));
				defect.setSubmitterID(resultSet.getInt("Submitter_ID"));
				defect.setAssigneeID(resultSet.getInt("Assignee_ID"));
				defect.setStatus(resultSet.getString("Status"));
				defect.setPriority(resultSet.getString("Priority"));
				defect.setSummary(resultSet.getString("Summary"));
				defect.setDetailedDescription(resultSet.getString("Detailed_Description"));
				defect.setResolutionNote(resultSet.getString("Final_Resolution"));
				defect.setStringOpenDate(resultSet.getString("Open_Date"));
				defect.setStringResolveDate(resultSet.getString("Closed_Date"));

				// Cleanup the connection and statement by closing them.

				cleanup();


				return defect;



		} catch (SQLException sqle) {

			LogException(sqle);
			return null;
		} catch (NullPointerException npe) {
			LogException(npe);
			return null;
		} catch (Exception e) {
			LogException(e);
			return null;
		}
	} // End getOpenDefect method

	// The getOpenDefectList method retrieves all open defects (meaning not CLOSED)
	// from the Defect table and provides an ArrayList of DefectBean.
	public synchronized ArrayList<DefectBean> getOpenDefectList() {

		try {

			ArrayList<DefectBean> defectList = new ArrayList<DefectBean>();

			String defectQuery = new String("SELECT * FROM DEFECT WHERE STATUS != 'CLOSED'");

			LogInfo(defectQuery);

			preparedStatement = connection.prepareStatement(defectQuery);

			// Execute query.
			runSQLstatement();

			// If result is null, return null.  Otherwise, create a list of all defects retrieved.
			if(resultSet == null) {
				LogWarning("DatabaseInterfaceDefect: Array Result set is null");
				return null;
			} else {
				// For each row returned...
				while (resultSet.next()) {

					// Create a new DefectBean and store values in it
					DefectBean defect = new DefectBean();
					defect.setDefectID(resultSet.getInt("Defect_ID"));
					defect.setApplicationID(resultSet.getInt("Application_ID"));
					defect.setSubmitterID(resultSet.getInt("Submitter_ID"));
					defect.setAssigneeID(resultSet.getInt("Assignee_ID"));
					defect.setStatus(resultSet.getString("Status"));
					defect.setPriority(resultSet.getString("Priority"));
					defect.setSummary(resultSet.getString("Summary"));
					defect.setDetailedDescription(resultSet.getString("Detailed_Description"));
					defect.setResolutionNote(resultSet.getString("Final_Resolution"));
					defect.setStringOpenDate(resultSet.getString("Open_Date"));
					defect.setStringResolveDate(resultSet.getString("Closed_Date"));

					// Add the DefectBean to the ArrayList
					defectList.add(defect);

				}

				// Close the statement and connection.
				cleanup();

				return defectList;
			} // End code that builds the defect list
		} catch (SQLException sqle) {

			LogException(sqle);
			return null;
		} catch (NullPointerException npe) {

			LogException(npe);
			return null;
		} catch (Exception e) {
			LogException(e);
			return null;
		}

	} // End getOpenDefectList method

	// The writeNewDefect method is meant for writing new defects
	// to the database.  If there is NO assignee, then set the
	// input parameter assignID to -1.  This will get changed to
	// NULL in the MySQL database.
	public synchronized boolean writeNewDefect(int appID, int submitID, int assignID,
		String defectPriority, String defectSummary, String defectDescription,
		String defectOpenDate) {

		try {

			// Here, we compose our command to insert a new defect object.

			String defectStatus = ((assignID == -1) ? new String("NEW") : new String("ASSIGNED"));

			String defectAssigneeID = ((assignID == -1) ? new String("NULL") : String.valueOf(assignID));



			/*String defectQuery = new String("INSERT INTO DEFECT (Application_ID,Submitter_ID,Assignee_ID, \n" +
				"Status,Priority,Summary,Detailed_Description,Open_Date) VALUES \n" +
				"(" + appID + "," + submitID + "," + defectAssigneeID + ",'" + defectStatus + "','" + defectPriority +
				"','" + defectSummary + "','" + defectDescription + "','" + defectOpenDate + "')");*/

			String defectQuery = new String("INSERT INTO DEFECT (Application_ID,Submitter_ID,Assignee_ID, \n" +
				"Status,Priority,Summary,Detailed_Description,Open_Date) VALUES \n" +
				"(?,?,?,?,?,?,?,?)");

			LogInfo(defectQuery);

			preparedStatement = connection.prepareStatement(defectQuery);
			preparedStatement.setInt(1,appID);
			preparedStatement.setInt(2,submitID);
			if(assignID == -1) {
				preparedStatement.setNull(3,Types.NULL);
			} else {
				preparedStatement.setInt(3,assignID);
			}

			preparedStatement.setString(4,defectStatus);
			preparedStatement.setString(5,defectPriority);
			preparedStatement.setString(6,defectSummary);
			preparedStatement.setString(7,defectDescription);
			preparedStatement.setString(8,defectOpenDate);

			// The runSQLstatement method returns true if nothing goes wrong.  If something goes wrong
			// it returns false.

			int rowCount = preparedStatement.executeUpdate();

			cleanup();

			// If the rowCount is zero, return false because we failed to insert.
			// Otherwise return true.
			return (rowCount != 0);

		} catch (SQLException sqle) {

			LogException(sqle);
			return false;
		} catch (NullPointerException npe) {

			LogException(npe);
			return false;
		} catch (Exception e) {
			LogException(e);
			return false;
		}


	} // End writeNewDefect method

	// The writeModifiedDefect method is meant for writing a modified defect
	// to the database. It accepts a Defect_ID along with the specific fields that
	// we allow to be updated (i.e. we don't allow changing the submitter ID or the application ID).

	// Pass in -1 for assignID (assigneeID) if we're not assigning to anyone while making this change.

	public synchronized boolean writeModifiedDefect(int defectID, int assignID, String status,
		String priority, String summary, String detailedDescription, String resolutionNote,
		String resolveDate) {

		try {

			// Since assignee ID is NULL if no one is assigned but is a numerical value equal to
			// a Person_ID from the Person table if someone is assigned, let's use a string.
			// The string for the assigneeID is "NULL" if the Defect object has a -1.
			// Otherwise, it is just the assigneeID in the defect.  We cannot use -1 in a
			// SQL table since this table references Person_ID in the Person table.

			String defectAssigneeID = ((assignID == -1) ? new String("NULL") : String.valueOf(assignID));


			/*String defectQuery = new String("UPDATE DEFECT SET\n" +
				"		Assignee_ID = " + defectAssigneeID + ",\n" +
				"		Status = '" + status + "',\n" +
				"		Priority = '" + priority + "',\n" +
				"		Summary = '" + summary + "',\n" +
				"		Detailed_Description = '" + detailedDescription + "',\n" +
				"		Final_Resolution = '" + resolutionNote + "',\n" +
				"		Closed_Date = " + resolveDate + " \n" +
				"	WHERE Defect_ID = " + defectID);*/


			String defectQuery = new String("UPDATE DEFECT SET\n" +
				"		Assignee_ID = ?,\n" +
				"		Status = ?,\n" +
				"		Priority = ?,\n" +
				"		Summary = ?,\n" +
				"		Detailed_Description = ?,\n" +
				"		Final_Resolution = ?,\n" +
				"		Closed_Date = ? \n" +
				"	WHERE Defect_ID = ?");

			preparedStatement = connection.prepareStatement(defectQuery);

			if(assignID == -1) {
				preparedStatement.setNull(1,Types.NULL);
			} else
			{
				preparedStatement.setInt(1,assignID);
			}

			preparedStatement.setString(2,status);
			preparedStatement.setString(3,priority);
			preparedStatement.setString(4,summary);
			preparedStatement.setString(5,detailedDescription);
			preparedStatement.setString(6,resolutionNote);
			preparedStatement.setString(7,resolveDate);
			preparedStatement.setInt(8,defectID);

			LogInfo(defectQuery);

			int rowCount = preparedStatement.executeUpdate();

			cleanup();

			// If the rowCount is zero we didn't update any row so return false.
			// Otherwise return true.
			return (rowCount != 0);

		} catch (SQLException sqle) {

			LogException(sqle);
			return false;
		} catch (NullPointerException npe) {

			LogException(npe);
			return false;
		} catch (Exception e) {
			LogException(e);
			// Return false because the method has failed given that we threw an exception.
			return false;
		}

	} // End writeModifiedDefect method

	// The writeClosedDefect method is meant for writing a closed.
	// It takes in a defect ID, a resolution Note, and a resolve date.

	public synchronized boolean writeClosedDefect(int defectID, String resolutionNote, String resolveDate) {

		try {

			/*String defectQuery = new String("UPDATE DEFECT SET\n" +
				"		Status = 'CLOSED',\n" +
				"		Final_Resolution = '" + resolutionNote + "',\n" +
				"		Closed_Date = '" + resolveDate + "' \n" +
				"	WHERE Defect_ID = " + defectID);*/

			String defectQuery = new String("UPDATE DEFECT SET\n" +
				"		Status = 'CLOSED',\n" +
				"		Final_Resolution = ?,\n" +
				"		Closed_Date = ? \n" +
				"	WHERE Defect_ID = ?");


			preparedStatement = connection.prepareStatement(defectQuery);
			preparedStatement.setString(1,resolutionNote);
			preparedStatement.setString(2,resolveDate);
			preparedStatement.setInt(3,defectID);

			LogInfo(defectQuery);

			int rowCount = preparedStatement.executeUpdate();

			cleanup();

			// If the rowCount is zero we didn't update any row so return false.
			// Otherwise return true.
			return (rowCount != 0);

		} catch (SQLException sqle) {

			LogException(sqle);
			return false;
		} catch (NullPointerException npe) {

			LogException(npe);
			return false;
		} catch (Exception e) {

			LogException(e);

			return false;
		}

	} // End writeClosedDefect method

} // End DatabaseInterface